package com.ce.util;


import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ImportBaseData {

	public static void main(String[] args) throws Exception {
		String file = "E:/辉煌公司项目/人员管理.xls";
		new ImportBaseData().importOrgPersonFromExcel(file);
	}
	
	ArrayList<String> departmentlist = new ArrayList<String>();
	ArrayList<String> peoplelist = new ArrayList<String>();
	
	
	public void importOrgPersonFromExcel(String file) throws Exception{
		//连接数据库
//		Connection con = DbUtil.getConnection(OracleDriver.class, "jdbc:oracle:thin:@172.16.18.155:1521:csmisdb", "sczhdt", "csmis");
//		DbUtil._CONNECTION = new UnClosedConnection(con);
	
		int nseq = 0;
		int mapKey = 1;
		int orgType = 0;
		int orgLevel = 0;
		
		
		int personChiefType = 0;
		

		HashMap <String,String> orgMap = new HashMap<String,String>();
		orgMap.put("大同电务段","dtdwd");
		DecimalFormat df = new DecimalFormat("0000");
		
		HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));//加载Excel文件
		HSSFSheet sheet = wb.getSheetAt(0);//选取第一个sheet
		for(int i=1;i<sheet.getLastRowNum();i++){//处理第一个SHEET
			HSSFRow row = sheet.getRow(i);//取Excel的行，行编号从0开始
			if(row==null)continue;
			
			boolean isFirst = false;
			
			
			String org = getCellVal(row.getCell((short) 3));//取行中对应列的单元格值，编号从0开始
			String judgeType = getCellVal(row.getCell((short) 4));
			String upOrg = getCellVal(row.getCell((short) 5));
			
			if(!orgMap.containsKey(org)){	
				mapKey++;
				isFirst = true;
				orgMap.put(org,"org"+ df.format(mapKey));		
			
				//sys_t_org部门表，vc_type段领导、段科室为空，车间6，工区7，vc_org$level，段领导7、段科室8、车间9、工区10
				//sys_t_person人员表，vc_log$name系统登录名，vc_seq性别，男0，女1，vc_chief$type人员职务等级
//				1			普通员工
//				2			车间干部
//				3			科室干部
//				4			车间正副主任
//				5			正副科长
//				6			段领导
//				vc_status用户状态0非系统用户，1系统用户,n_seq人员姓名显示顺序
				if(judgeType.equals("段领导") ){
					orgLevel = 7;
					personChiefType = 6;
				}
				else if (judgeType.equals("段科室") ){ 
					orgLevel = 8;
					if(isFirst == true) personChiefType = 5;
					else personChiefType = 3;
				}
				else if(judgeType.equals("车间")){
					orgType = 6;
					orgLevel = 9;
					if(isFirst == true) personChiefType = 4;
					else personChiefType = 2;
				}
				else if (judgeType.equals("工区")){
					orgType = 7;
					orgLevel = 10;
				    personChiefType = 1;
				}
				
				String orgId = "org"+ df.format(mapKey);
				String orgParId = orgMap.get(upOrg);
				
				departmentlist.add("insert into sys_t_org(vc_id,vc_name,vc_par$orgid,vc_type,vc_org$level,vc_del$flag)" +
						"\nvalues('"+orgId+"','"+org+"','"+orgParId+"','"+(orgType==0?"":""+orgType)+"','"+orgLevel+"',0);");		
			}
			
			
			
			//String pid = getCellVal(row.getCell((short) 4));
			//id=id==null?id:id.replaceAll("\\.0$", "");//正则表达式替换，替换掉末尾的.0
			//if(DbUtil.getFirstIntSqlQuery("select count(*) from jc_t_alarm$dict$table where vc_dict$code=?",new Object[]{id})>0){
				//System.out.println(id+"\t"+name+"\t"+pid+"\t");
			//}
			//else{
				//list.add("insert into sys_t_org(vc_id,vc_name,vc_par$orgid,vc_type,vc_org$level)" +
				//	"\nvalues('"+department+"','"+name+"','"+id+"','',1,0,0,'"+getId(pid)+"',sysdate,'系统','');");
			//}
			String name = getCellVal(row.getCell((short) 0));//取行中对应列的单元格值，编号从0开始
//			String position = getCellVal(row.getCell((short) 1));
//			String telephone = getCellVal(row.getCell((short)4));
			nseq++;
			String personId = "person"+df.format(nseq);
			
			
			if(judgeType.equals("段领导") ){
				orgLevel = 7;
				personChiefType = 6;
			}
			else if (judgeType.equals("段科室") ){ 
				orgLevel = 8;
				if(isFirst == true) personChiefType = 5;
				else personChiefType = 3;
			}
			else if(judgeType.equals("车间")){
				orgType = 6;
				orgLevel = 9;
				if(isFirst == true) personChiefType = 4;
				else personChiefType = 2;
			}
			else if (judgeType.equals("工区")){
				orgType = 7;
				orgLevel = 10;
			    personChiefType = 1;
			}
			

			peoplelist.add("insert into sys_t_person(vc_id,vc_name,vc_log$name,vc_org$id,vc_sex,vc_chief$type,vc_status,n_seq)" +
					"\nvalues('"+personId+"','"+name+"','"+name+"','"+orgMap.get(org)+"','','"+personChiefType+"','0',"+nseq+");");
			
		}
		System.out.println("\n\n\n\n\n\n\n");
		for(String s:departmentlist){
			System.out.println(s);
			String outFile = "E:/辉煌公司项目/1.txt";
			try{
				FileWriter fileWriter =  new FileWriter(outFile,true);
				PrintWriter printWriter =  new PrintWriter(fileWriter);
				printWriter.write(s);
				printWriter.println();
				printWriter.close();
				fileWriter.close();
			}
			catch(IOException e){
				System.out.println("Errors when writting the result file!");
				System.exit(1);
			}
		}
		for(String s:peoplelist){
			System.out.println(s);
			String outFile = "E:/辉煌公司项目/1.txt";
			try{
				FileWriter fileWriter =  new FileWriter(outFile,true);
				PrintWriter printWriter =  new PrintWriter(fileWriter);
				printWriter.write(s);
				printWriter.println();
				printWriter.close();
				fileWriter.close();
			}
			catch(IOException e){
				System.out.println("Errors when writting the result file!");
				System.exit(1);
			}
		}
	}

	private String getCellVal(HSSFCell cell){
		if(cell==null){
			return "";
		}
		try {
			return cell.getStringCellValue();
		} catch (Exception e) {
			return ""+cell.getNumericCellValue();
		}
	}
}
